package com.practice.springboot_01_zwj.util;
import com.practice.springboot_01_zwj.entity.IncomeQuestion;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class ExcelExportUtil {

    private static final SimpleDateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd");

    public static byte[] exportIncomeQuestionList(List<IncomeQuestion> dataList) throws Exception {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("项目明细数据");

            // 表头 - 根据示例数据字段创建
            String[] headers = {
                    "全网商机编码","全网商机编号","全网项目编码","商机名称","项目名称","集团客户名称",
                    "归属省","归属地市","地市编码","县域组织名称（公开市场）","所属行业",
                    "后向合作伙伴名称","是否招投标项目","1阶1星首次归档时间","1阶2星首次归档时间",
                    "售前支撑开始时间","售前支撑完成时间", "预决策完成时间", "中标时间", "立项完成时间",
                    "项目首次签约时间", "项目开工时间", "项目实际交付时间（终验）", "合同要求时间",
                    "合同履约开始时间", "合同履约结束时间","上月计划出账金额含税（元）", "上月计划出账金额不含税（元）", "上月实际出账金额含税（元）", "上月实际出账金额不含税（元）", "上月计划支出金额含税（元）", "上月计划支出金额不含税（元）", "上月实际报账金额含税（元）", "上月实际报账金额不含税（元）",
                    "是否预立项超30天未中标", "是否逾期交付", "是否逾期交底", "是否逾期签约", "前向合同名称", "前向合同编码", "后向合同名称", "后向合同编码",
                    "项目合同总金额含税（元）", "项目合同总金额不含税（元）", "后向合同签约金额含税（元）", "后向合同签约金额不含税（元）", "it通服收入含税（元）", "it通服收入不含税（元）", "ct通服收入含税（元）", "ct通服收入不含税（元）", "非通服收入含税（元）", "非通服收入不含税（元）",
                    "当前阶段", "是否已结项", "是否续签项目", "项目合同模式", "项目实施类型",
                    "项目整体利润率（概算）", "IT部分利润率（概算）", "项目整体利润率（预算）", "IT部分利润率（预算）", "项目整体利润率（核算）", "IT部分利润率（核算）","动态回收期（决算）", "静态回收期（决算）"
            };
            // 创建表头行
            Row headerRow = sheet.createRow(0);
            CellStyle headerStyle = createHeaderStyle(workbook);
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(headerStyle);
                sheet.setColumnWidth(i, 20 * 256); // 20个字符宽度
            }

            // 填充数据行
            int rowNum = 1;
            for (IncomeQuestion item : dataList) {
                Row row = sheet.createRow(rowNum++);

                int colIndex = 0;
                row.createCell(colIndex++).setCellValue(item.getBusiOpportunityNumber());
                row.createCell(colIndex++).setCellValue(item.getBusiOpportunityCode());
                row.createCell(colIndex++).setCellValue(item.getGroupProjectCode());
                row.createCell(colIndex++).setCellValue(item.getBusiOpportunityName());
                row.createCell(colIndex++).setCellValue(item.getProjectName());
                row.createCell(colIndex++).setCellValue(item.getCustomerName());
                row.createCell(colIndex++).setCellValue(item.getProvinceCode());
                row.createCell(colIndex++).setCellValue(item.getCityName());
                row.createCell(colIndex++).setCellValue(item.getCityCode());
                row.createCell(colIndex++).setCellValue(item.getPublicCountyCode());
                row.createCell(colIndex++).setCellValue(convertIndustry(item.getIndustrycounty()));
                row.createCell(colIndex++).setCellValue(item.getPartnerName());

                // 修改点1：处理是否招投标项目（Y->是, N->否）
                row.createCell(colIndex++).setCellValue(convertYnToChinese(item.getBidFlag()));

                // 日期字段格式化处理
                row.createCell(colIndex++).setCellValue(formatDate(item.getSjFirstTime()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getPreSaleSupportLaunchTime()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getPreSaleSupportStartTime()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getPreSaleSupportFinishTime()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getPreProjectDate()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getWinningTime()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getProjectFinishTime()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getProSigningFirstTime()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getActStartTime()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getActualDeliveryTime()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getContractDeliveryTime()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getContractBeginTime()));
                row.createCell(colIndex++).setCellValue(formatDate(item.getContractEndTime()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getMonthPlanBillAmountHan()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getMonthPlanBillAmountNone()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getMonthActualBillAmountHan()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getMonthActualReportAmountNone()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getMonthPlanExpenseAmountHan()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getMonthPlanExpenseAmountNone()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getMonthActualReportAmountHan()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getMonthActualReportAmountNone()));

                // 修改点2：统一处理所有YN标志字段
                row.createCell(colIndex++).setCellValue(convertYnToChinese(item.getWinFlag()));    // 是否预立项超30天未中标
                row.createCell(colIndex++).setCellValue(convertYnToChinese(item.getJfFlag()));     // 是否逾期交付
                row.createCell(colIndex++).setCellValue(convertYnToChinese(item.getJdFlag()));     // 是否逾期交底
                row.createCell(colIndex++).setCellValue(convertYnToChinese(item.getQyFlag()));     // 是否逾期签约

                row.createCell(colIndex++).setCellValue(item.getContractName());
                row.createCell(colIndex++).setCellValue(item.getContractCode());
                row.createCell(colIndex++).setCellValue(item.getAfterContractName());
                row.createCell(colIndex++).setCellValue(item.getAfterContractCode());
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getMoneyTax()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getWinBidMoney()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getAfterContractAmount()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getAfterContractAmountNone()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getItServiceRevenueAmount()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getItServiceRevenueAmountNone()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getCtServiceRevenueAmount()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getCtServiceRevenueAmountNone()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getNonServiceRevenueAmount()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getNonServiceRevenueAmountNone()));
                row.createCell(colIndex++).setCellValue(item.getCurrentStage());

                // 修改点3：继续处理YN标志字段
                row.createCell(colIndex++).setCellValue(convertYnToChinese(item.getJxFlag()));     // 是否已结项
                row.createCell(colIndex++).setCellValue(convertYnToChinese(item.getXqState()));     // 是否续签项目

                row.createCell(colIndex++).setCellValue(convertProjectContractCode(item.getProjectContractCode()));
                row.createCell(colIndex++).setCellValue(convertProjImplementationType(item.getProjImplementationType()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getTotalProfit()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getItProfit()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getProfitMargin()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getItNpv()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getProfitMarginHe()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getItIpr()));
                row.createCell(colIndex++).setCellValue(getDoubleValue(item.getDynamicYear()));
                row.createCell(colIndex).setCellValue(getDoubleValue(item.getStaticYear()));
            }

            // 写入字节流
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            workbook.write(bos);
            return bos.toByteArray();
        }
    }

    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 10);
        style.setFont(font);
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(true); // 自动换行
        return style;
    }

    private static String formatDate(Date date) {
        return date != null ? DATE_FORMAT.format(date) : "";
    }

    private static double getDoubleValue(Double value) {
        return value != null ? value : 0.0;
    }
    private static String convertYnToChinese(String value) {
        if (value == null) return "";
        switch (value) {
            case "Y":
                return "是";
            case "N":
                return "否";
            default:
                return value; //
        }
    }
    // 所属行业编码转文字
    private static String convertIndustry(String code) {
        if (code == null) return "";
        switch (code) {
            case "1": return "医疗";
            case "2": return "教育";
            case "3": return "金融";
            case "4": return "交通";
            case "5": return "商客";
            case "6": return "工业能源";
            case "7": return "融合创新";
            case "8": return "党政军";
            case "9": return "互联网";
            case "10": return "农业文旅";
            default: return code;
        }
    }
    // 项目合同模式编码转文字
    private static String convertProjectContractCode(String code) {
        if (code == null) return "";
        switch (code) {
            case "10": return "普通项目";
            case "20": return "框架订单项目集(非总分框架项目)";
            case "30": return "框架结算项目集(总分框架项目)";
            case "40": return "统谈分签项目集";
            case "50": return "框架子项目";
            case "60": return "统谈分签子项目";
            default: return code;
        }
    }

    // 项目实施类型编码转文字
    private static String convertProjImplementationType(String code) {
        if (code == null) return "";
        switch (code) {
            case "10": return "纯建设";
            case "20": return "纯运维";
            case "30": return "建设+运维";
            default: return code;
        }
    }

}